package east2west.dao;

import east2west.dto.SaleTourDTO;
import east2west.dto.TourDTO;
import east2west.dto.TourDetailDTO;
import east2west.dto.TourTypeDTO;
import east2west.interfaces.TourInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author thanhluan
 */
public class TourDAO implements TourInterface {

    Connection con = null;
    CallableStatement call = null;
    TourDTO dto = null;
    ResultSet rs = null;

    public ArrayList<TourDTO> readByAll() {

        try {
            TourDetailDAO tourDetailDAO = new TourDetailDAO();
            TourDetailDTO tourDetailDTO = new TourDetailDTO();
            TourTypeDAO tourTypeDAO = new TourTypeDAO();
            //TourPriceDAO tourPriceDao =new TourPriceDAO();
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Tour");
            rs = call.executeQuery();
            ArrayList<TourDTO> list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                tourDetailDTO = tourDetailDAO.getTopOnlyImage(rs.getInt(1));
                dto.setTourDetailDTO(tourDetailDTO);
                dto.setTourId(tourDetailDTO.getTourId());

                //dto.setTourId(rs.getInt(1));
                TourTypeDTO typeDTO = tourTypeDAO.findById(rs.getInt(2));
                dto.setTourTypeDTO(typeDTO);
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                // TourPriceDTO pricedto = new TourPriceDTO();


                //dto.setIsDelete(rs.getByte(8));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {

            return null;

        }
    }

    public ArrayList<TourDTO> readByAllDeparture() {

        try {

            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT distinct Departure from V_Tour");
            rs = call.executeQuery();
            ArrayList<TourDTO> list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setDeparture(rs.getString(1).trim());
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {

            return null;

        }
    }

    public ArrayList<TourDTO> readByAllDestination() {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT distinct Destination from V_Tour");
            rs = call.executeQuery();
            ArrayList<TourDTO> list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setDestination(rs.getString(1).trim());
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {

            return null;

        }
    }

    public ArrayList<TourDTO> findByName(String tourName) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Tour_FindName(?)}");
            call.setString(1, tourName);
            rs = call.executeQuery();
            ArrayList<TourDTO> list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setTourId(rs.getInt(1));
                dto.setTypeId(rs.getInt(2));
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setIsDelete(rs.getByte(8));
                list.add(dto);
            }

            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }
    }

    public ArrayList<TourDTO> findByDeparture(String departure) {
        ArrayList<TourDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Tour_FindDeparture(?)}");
            call.setString(1, departure);
            rs = call.executeQuery();
            list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setTourId(rs.getInt(1));
                dto.setTypeId(rs.getInt(2));
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setIsDelete(rs.getByte(8));
                list.add(dto);
            }

            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;

        }
    }

    public ArrayList<TourDTO> findByDestination(String destination) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<TourDTO> findByDuration(int duration) {
        ArrayList<TourDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Tour_FindDuration(?)}");
            call.setInt(1, duration);
            rs = call.executeQuery();
            list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setTourId(rs.getInt(1));
                dto.setTypeId(rs.getInt(2));
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setIsDelete(rs.getByte(8));

                list.add(dto);
            }

            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;

        }
    }

    public TourDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Tour_FindId(?)}");
            call.setInt(1, id);
            rs = call.executeQuery();
            TourTypeDAO tourTypeDAO = new TourTypeDAO();

            TourDetailDAO tourDetailDAO = new TourDetailDAO();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setTourId(id);
                TourDetailDTO detailDTO = tourDetailDAO.getTopOnlyImage(rs.getInt(1));
                dto.setTourDetailDTO(detailDTO);
                TourTypeDTO tourTypeDTO = tourTypeDAO.findById(rs.getInt(2));
                dto.setTourTypeDTO(tourTypeDTO);
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));


            }

            rs.close();
            call.close();
            return dto;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourDTO> findByTourShowTop() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Tour");
            rs = call.executeQuery();
            TourTypeDAO tourTypeDAO = new TourTypeDAO();
            ArrayList<TourDTO> listTourShow = new ArrayList<TourDTO>();
            TourDetailDAO tourDetailDAO = new TourDetailDAO();
            while (rs.next()) {
                dto = new TourDTO();
                //dto.setTourId(rs.getInt(1));

                TourDetailDTO detailDTO = tourDetailDAO.getTopOnlyImage(rs.getInt(1));
                dto.setTourDetailDTO(detailDTO);
                TourTypeDTO tourTypeDTO = tourTypeDAO.findById(rs.getInt(2));
                dto.setTourTypeDTO(tourTypeDTO);
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setTourId(detailDTO.getTourId());
                listTourShow.add(dto);

            }
            rs.close();
            call.close();
            return listTourShow;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourDTO> findByTourShowTopMenu() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT Top(6)* FROM V_Tour");
            rs = call.executeQuery();
            TourTypeDAO tourTypeDAO = new TourTypeDAO();
            ArrayList<TourDTO> listTourShow = new ArrayList<TourDTO>();
            TourDetailDAO tourDetailDAO = new TourDetailDAO();
            while (rs.next()) {
                dto = new TourDTO();
                //dto.setTourId(rs.getInt(1));

                TourDetailDTO detailDTO = tourDetailDAO.getTopOnlyImage(rs.getInt(1));
                dto.setTourDetailDTO(detailDTO);
                TourTypeDTO tourTypeDTO = tourTypeDAO.findById(rs.getInt(2));
                dto.setTourTypeDTO(tourTypeDTO);
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setTourId(detailDTO.getTourId());
                listTourShow.add(dto);

            }
            rs.close();
            call.close();
            return listTourShow;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourDTO> findByDto(TourDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Search_Tour(?,?,?,?,?)}");
            // call.setString(1, dto.getTourName());
            call.setString(1, dto.getDeparture());
            call.setString(2, dto.getDestination());
            call.setInt(3, dto.getDuration());
            call.setString(4, dto.getTourTypeDTO().getTourTypeName());
            call.setString(5, dto.getLocationDTO().getLocationName());
            rs = call.executeQuery();
            TourDetailDAO detaildao = new TourDetailDAO();
            ArrayList<TourDTO> listSearch = new ArrayList<TourDTO>();
            while (rs.next()) {
                TourDTO tour = new TourDTO();
                TourDetailDTO detail = new TourDetailDTO();
                detail = detaildao.getTopOnlyImage(rs.getInt(1));
                tour.setTourDetailDTO(detail);
                tour.setTourId(detail.getTourId());
                tour.setTourName(rs.getString(2).trim());
                tour.setIntroduction(rs.getString(3).trim());
                tour.setDeparture(rs.getString(4).trim());
                tour.setDestination(rs.getString(5).trim());
                tour.setDuration(rs.getInt(6));
                TourTypeDTO type = new TourTypeDTO();
                tour.setTypeId(rs.getInt(7));
                type.setTourTypeName(rs.getString(8).trim());
                tour.setTourTypeDTO(type);
                listSearch.add(tour);
            }
            return listSearch;


            // return listSearch;

        } catch (SQLException ex) {
            return null;
        }

    }

    public int create(TourDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_Tour(?,?,?,?,?,?,?)}");
            call.setInt(1, dto.getTypeId());
            call.setString(2, dto.getTourName());
            call.setString(3, dto.getIntroduction());
            call.setString(4, dto.getDeparture());
            call.setString(5, dto.getDestination());
            call.setInt(6, dto.getDuration());
            call.registerOutParameter(7, java.sql.Types.INTEGER);
            call.executeUpdate();
            int tourId = call.getInt(7);
            call.close();
            return tourId;
        } catch (SQLException ex) {
            return 0;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }

            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(TourDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_Tour(?,?,?,?,?,?,?)}");
            call.setInt(1, dto.getTourId());
            call.setInt(2, dto.getTypeId());
            call.setString(3, dto.getTourName());
            call.setString(4, dto.getIntroduction());
            call.setString(5, dto.getDeparture());
            call.setString(6, dto.getDestination());
            call.setInt(7, dto.getDuration());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }

            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(TourDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Tour(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<TourDTO> readByTopTour() {

        try {

            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * From V_Top_Tour");
            rs = call.executeQuery();
            ArrayList<TourDTO> list = new ArrayList<TourDTO>();
            while (rs.next()) {
                dto = new TourDTO();
                dto.setTourId(rs.getInt(1));
                dto.setTypeId(rs.getInt(2));
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                dto.setIsDelete(rs.getByte(8));
                list.add(dto);
            }

            return list;
        } catch (Exception ex) {
            System.out.print("thong tin hien thi " + ex);
            return null;

        }
    }

    public ArrayList<TourDTO> findByOrtherTour(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Tour Where TourId != ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            TourTypeDAO tourTypeDAO = new TourTypeDAO();
            ArrayList<TourDTO> listOrtherTour = new ArrayList<TourDTO>();
            TourDetailDAO tourDetailDAO = new TourDetailDAO();
            while (rs.next()) {
                dto = new TourDTO();
                // dto.setTourId(id);
                TourDetailDTO detailDTO = tourDetailDAO.getTopOnlyImage(rs.getInt(1));
                dto.setTourDetailDTO(detailDTO);
                TourTypeDTO tourTypeDTO = tourTypeDAO.findById(rs.getInt(2));
                dto.setTourTypeDTO(tourTypeDTO);
                dto.setTourName(rs.getString(3).trim());
                dto.setIntroduction(rs.getString(4).trim());
                dto.setDeparture(rs.getString(5).trim());
                dto.setDestination(rs.getString(6).trim());
                dto.setDuration(rs.getInt(7));
                listOrtherTour.add(dto);

            }

            rs.close();
            call.close();
            return listOrtherTour;
        } catch (SQLException ex) {
            return null;
        }
    }
    //Sale Tour Order
    //Total thoe TypeTour
    //1. Display OrderTour where TourType = TypeId
    //2. Display sum(OrderTour) where TourType = TypeId

    public float totalTypeName(int id) {
        float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT TypeId,SUM(Amount) AS 'TONG' FROM V_SaleTour WHERE TypeId=? GROUP BY TypeId");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("TONG");

            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public ArrayList<SaleTourDTO> listAll() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_SaleTour");
            ArrayList<SaleTourDTO> listsale = new ArrayList<SaleTourDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                SaleTourDTO dtos = new SaleTourDTO();
                dtos.setTourId(rs.getInt(1));
                dtos.setTypeId(rs.getInt(2));
                dtos.setTourName(rs.getString(3).trim());
                dtos.setDeparture(rs.getString(4).trim());
                dtos.setDestination(rs.getString(5).trim());
                dtos.setDuration(rs.getInt(6));
                dtos.setTourServiceName(rs.getString(7).trim());
                dtos.setTypeName(rs.getString(8).trim());
                dtos.setOrderId(rs.getInt(9));


                dtos.setTotalQty(rs.getInt(10));
                dtos.setChidlQty(rs.getInt(11));
                dtos.setAmount(rs.getFloat(12));
                dtos.setCustId(rs.getInt(13));
                Date dt = rs.getDate(14);
                if (dt == null) {
                    dtos.setOrderDate("");
                } else {
                    dtos.setOrderDate(DateTimeUtil.convert2String(dt, "MM-dd-yyyy"));
                }
                Date date = rs.getDate(19);
                if (date == null) {
                    dtos.setStartDate("");
                } else {
                    dtos.setStartDate(DateTimeUtil.convert2String(date, "MM-dd-yyyy"));
                }
                listsale.add(dtos);

            }
            return listsale;
        } catch (SQLException ex) {
            return null;
        }

    }

    public ArrayList<SaleTourDTO> getListFillter() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select TourName,Departure,Destination,TypeName,FullName,Email,JobName,Duration,day(OrderDate) as 'Day',month(OrderDate) as 'Month',year(OrderDate) as 'Year',Sum(Amount) as 'Total' from V_SaleTour group by Tourname,Departure,TypeName,Duration,Destination,OrderDate,FullName,Email,JobName");
            ArrayList<SaleTourDTO> listFillter = new ArrayList<SaleTourDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                SaleTourDTO dtoF = new SaleTourDTO();
                dtoF.setTourName(rs.getString(1).trim());
                dtoF.setDeparture(rs.getString(2).trim());
                dtoF.setDestination(rs.getString(3).trim());
                dtoF.setTypeName(rs.getString(4).trim());
                dtoF.setFullName(rs.getString(5).trim());
                dtoF.setEmail(rs.getString(6).trim());
                dtoF.setJobName(rs.getString(7).trim());
                dtoF.setDuration(rs.getInt(8));
                dtoF.setDay(rs.getInt("Day"));
                dtoF.setMonth(rs.getInt("Month"));
                dtoF.setYear(rs.getInt("Year"));
                dtoF.setTotal(rs.getFloat("Total"));
                listFillter.add(dtoF);
            }
            return listFillter;
        } catch (SQLException ex) {
            return null;
        }
    }

    public float totaDuration(int duration) {
        float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT SUM(Amount) as 'TotalD' from V_SaleTour where Duration=?");
            call.setInt(1, duration);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("TotalD");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float totalCost(float price1, float price2) {
        float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT sum(Amount) as 'TotalP' from V_SaleTOur where Price between ? and ? ");
            call.setFloat(1, price1);
            call.setFloat(2, price2);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("TotalP");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public float totalSeason(int month1, int month2) {

        float sum = 0;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Amount)as 'TotalS' from V_SaleTour where month(StartDate) between ? and ?");
            call.setFloat(1, month1);
            call.setFloat(2, month2);
            rs = call.executeQuery();
            while (rs.next()) {
                sum = rs.getFloat("TotalS");
            }
            return sum;
        } catch (SQLException ex) {
            return 0;
        }
    }
}
